﻿Public Class datos
    Enum tipo_conexion
        transaccion
        simple
    End Enum
    Enum estado_transaccion
        _ok
        _error
    End Enum

    'Dim cadena As String = "Data Source=NOTEBOOK-ANGELO\sqlexpress;Initial Catalog=bd_pav;Integrated Security=True"
    'Dim cadena As String = "Data Source=ANGELO-PC\sqlexpress;Initial Catalog=bd_pav;Integrated Security=True"
    Dim cadena As String = "Data Source=Micheletti\sqlexpress;Initial Catalog=bd_pav;Integrated Security=True"
    Dim cmd As New SqlClient.SqlCommand
    Dim conexion As New SqlClient.SqlConnection
    Dim configurar_conexion As tipo_conexion = tipo_conexion.simple
    Dim control_transaccion As estado_transaccion = estado_transaccion._ok
    Dim transaccion As Object

    Property _cadena As String
        Get
            Return cadena
        End Get
        Set(ByVal value As String)
            cadena = value
        End Set
    End Property

    Public Sub iniciar_conexion_transaccion()
        Me.configurar_conexion = tipo_conexion.transaccion
        Me.control_transaccion = estado_transaccion._ok
    End Sub

    Public Sub conectar()
        If Me.conexion.State.ToString <> "Open" Then
            conexion.ConnectionString = cadena
            conexion.Open()
            cmd.CommandType = CommandType.Text
            cmd.Connection = conexion

            If Me.configurar_conexion = tipo_conexion.transaccion Then
                transaccion = conexion.BeginTransaction
                cmd.Transaction = transaccion
            End If
        End If
    End Sub

    Public Function finalizar_conexion_transaccion() As estado_transaccion
        If control_transaccion = estado_transaccion._ok Then
            transaccion.Commit()
        Else
            transaccion.Rollback()
        End If

        conexion.Close()
        configurar_conexion = tipo_conexion.simple

        Return control_transaccion
    End Function

    Public Function leo_tabla(ByVal tabla As String) As Data.DataTable
        Dim datos As New Data.DataTable
        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select * from " & tabla
        datos.Load(cmd.ExecuteReader())
        conexion.Close()
        Return datos
    End Function
    Public Function ejecutar_sql(ByVal sql As String) As Data.DataTable
        Dim datos As New Data.DataTable
        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = sql
        datos.Load(cmd.ExecuteReader())
        conexion.Close()
        Return datos
    End Function

    Function leo_tabla(ByVal tabla As String, ByVal condicion As String) As DataTable
        Dim datos As New Data.DataTable
        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select * from " & tabla & " where " & condicion
        datos.Load(cmd.ExecuteReader())
        conexion.Close()
        Return datos
    End Function

    Public Function consulta(ByVal comando As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        Me.cmd.CommandText = comando
        Try
            tabla.Load(Me.cmd.ExecuteReader())
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error en consulta")
        End Try

        If Me.configurar_conexion = tipo_conexion.simple Then
            Me.conexion.Close()
        End If
        Return tabla

    End Function

    Public Function ejecutar(ByVal comando As String) As String
        Me.conectar()
        Me.cmd.CommandText = comando
        Try
            cmd.ExecuteNonQuery()
            Return "ok"
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Me.control_transaccion = estado_transaccion._error
            Return ex.Message
        End Try
        If Me.configurar_conexion = tipo_conexion.simple Then
            Me.conexion.Close()
        End If
    End Function

End Class

